package cn.uniquetech.hky;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GenerateCodeSqlServer {

	// 创建人名称
	public static final String cjrName = "wty";
	public static final String SAVEPATH = "d:\\";
	public static final String DBNAME = "CLFJY_exchange";
	public static final String PACKAGENAME = "cn.uniquesoft";
//	private static final String URL = "jdbc:mysql://139.196.177.17:3306/huopu";
	private static final String URL = "jdbc:sqlserver://192.168.115.80:1433;databaseName=CLFJY_exchange";
//	private static final String NAME = "root";
	private static final String NAME = "sa";
//	private static final String PASS = "root1234";
	private static final String PASS = "123456";
//	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	private List<String> tableDTNames = new ArrayList<String>(); // 数据表名称
	private List<String> tableVONames = new ArrayList<String>(); // 数据表对应的vo名称
	private List<ColumnVO> columnList = new ArrayList<ColumnVO>();

	private String getSql(String tbName) {
		tbName = tbName.toLowerCase();
		String sql = "";
//		sql = "select * from ex_users;";
		sql = "SELECT \n" +
				"    tbname       =  d.name,\n" +
				"    tbremarks     = CONVERT(varchar(200),isnull(f.value,'')),\n" +
				"    colno   = a.colorder,\n" +
				"    colname     = a.name,\n" +
				"    \n" +
				"    coltype       = b.name,\n" +
				"    \n" +
				"    LENGTH       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),\n" +
				"   \n" +
				"    ISNULL     = case when a.isnullable=1 then 'YES' else 'NO' end,\n" +
				"    coldefault     = isnull(e.text,''),\n" +
				"    remarks   = CONVERT(varchar(200),isnull(g.[value],''))\n" +
				"FROM \n" +
				"    syscolumns a\n" +
				"left join \n" +
				"    systypes b \n" +
				"on \n" +
				"    a.xusertype=b.xusertype\n" +
				"inner join \n" +
				"    sysobjects d \n" +
				"on \n" +
				"    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'\n" +
				"left join \n" +
				"    syscomments e \n" +
				"on \n" +
				"    a.cdefault=e.id\n" +
				"left join \n" +
				"sys.extended_properties   g \n" +
				"on \n" +
				"    a.id=G.major_id and a.colid=g.minor_id  \n" +
				"left join\n" +
				"sys.extended_properties f\n" +
				"on \n" +
				"    d.id=f.major_id and f.minor_id=0\n" +
				"where \n" +
				"    d.name='" + tbName +"' \n" +
				"order by \n" +
				"    a.id,a.colorder";
		/*sql = "SELECT a.*,b.table_comment AS tbremarks FROM (\r\n" + "SELECT is_nullable AS ISNULL,ISNULL(column_default,'') AS coldefault,column_comment AS remarks,ISNULL(character_maximum_length,0) AS LENGTH,column_name AS colname,ordinal_position AS colno,\r\n" + "data_type AS coltype,table_name AS tbname FROM information_schema.columns WHERE table_schema = '" + DBNAME + "' AND table_name='"
				+ tbName + "' ) a \r\n" + "LEFT JOIN (SELECT * FROM information_schema.tables WHERE table_schema = '" + DBNAME + "' AND table_name='" + tbName + "') b ON a.tbname=b.table_name ORDER BY a.colno ASC";*/
		System.out.println(sql);
		return sql;
	}

	private void GenDBCode() {
		tableDTNames.add("EX_users");
		tableVONames.add("User");

		try {
			Class.forName(DRIVER);
			Connection con = DriverManager.getConnection(URL, NAME, PASS);

			PreparedStatement ps = null;
			ResultSet rs = null;

			for (int i = 0; i < tableDTNames.size(); i++) {
				String tbName = tableDTNames.get(i);
				ColumnVO columnVO = null;
				tbName = tbName.toLowerCase();
				ps = con.prepareStatement(getSql(tbName));
				rs = ps.executeQuery();
				while (rs.next()) {
					columnVO = new ColumnVO();
					columnVO.setIsNull(rs.getString("isnull"));
					columnVO.setColDefault(rs.getString("coldefault").trim());
					columnVO.setColRemarks(rs.getString("remarks").trim());
					columnVO.setColLength(rs.getInt("length"));
					columnVO.setColName(rs.getString("colname").trim());
					columnVO.setColNo(rs.getInt("colno"));
					columnVO.setColType(rs.getString("coltype").trim());
					columnVO.setTbName(rs.getString("tbname").trim());
					columnVO.setTbRemarks(rs.getString("tbremarks").trim());
					columnVO.setVoName(tableVONames.get(i));
					columnList.add(columnVO);
				}
				GenVOUtil.genVOStr(columnList);
				GenMapperUtil.genMapperStr(columnList);
				GenSqlUtil.paraseSql(columnVO.getTbName(), columnVO.getVoName(), columnVO.getTbRemarks(), columnList);
				GenServiceUtil.parseService(columnVO.getVoName(), columnVO.getTbRemarks());
				GenControllerUtil.parseControllerClass(columnVO.getVoName(), columnVO.getTbRemarks());
				GenViewUtil.parseView(columnVO.getTbName(), columnVO.getVoName(), columnVO.getTbRemarks(), columnList);
			}
			System.out.print("ok,保存路径:" + GenerateCodeSqlServer.SAVEPATH + GenerateCodeSqlServer.DBNAME);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		(new GenerateCodeSqlServer()).GenDBCode();
	}
}
